1.使用EPPlus讀取.xlsx檔,需安裝套件 using OfficeOpenXml;
public static DataTable ExcelImport()
{
string path="你的excel路徑"
//開啟已存在的Excel檔案
FileInfo existingFile = new FileInfo(path);
//new一個包存取.xlsx檔
ExcelPackage package = new ExcelPackage(existingFile);
//因為EPPlus 升版和授權,需要加這行
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
//選取第1個sheet
ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
//取得所選sheet的列數和行數
int rows = worksheet.Dimension.End.Row;
int cols = worksheet.Dimension.End.Column;
//讀取資料放入DataTable
DataTable dt = new DataTable(worksheet.Name);
DataRow dr = null;
//ExcelWorksheet在.net framwork 的index從1開始;在.net從0開始
for (int i = 1; i <= rows; i++)
{
if (i > 1)
dr = dt.Rows.Add();
for (int j = 1; j <= cols; j++)
{
//把第一列設為DataTable標頭
if (i == 1)
dt.Columns.Add(worksheet.Cells[i, j].Value.ToString());
//剩下的寫入DataTable,不要加ToString(),因為若儲存格空白會導致NullReference
else
dr[j - 1] = worksheet.Cells[i, j].Value;
}
}
return dt;
}
2.使用EPPlus匯出Excel(以WinForm DataGridView為例)
//建立datatable
DataTable dt = new DataTable();
//加入 Columns
dt.Columns.Add(new DataColumn(dataGridView.Columns[0].HeaderText, typeof(int)));
dt.Columns.Add(new DataColumn(dataGridView.Columns[1].HeaderText, typeof(string)));
dt.Columns.Add(new DataColumn(dataGridView.Columns[2].HeaderText, typeof(string)));
dt.Columns.Add(new DataColumn(dataGridView.Columns[3].HeaderText, typeof(string)));
//加入 Rows,Count-1是dataGridView有預設空白列
for (int i = 0; i < dataGridView.Rows.Count - 1; i++)
{
DataRow dr = dt.NewRow();
for (int j = 0; j < dataGridView.Rows[i].Cells.Count; j++)
dr[j] = dataGridView.Rows[i].Cells[j].Value;
dt.Rows.Add(dr);
}
using (ExcelPackage package = new ExcelPackage())
{
string path;
SaveFileDialog save = new SaveFileDialog();
save.FileName = $"{comboboxAward.Text}_{labelAwardQuota.Text}_{DateTime.Now:yyyyMMdd}.xlsx";
save.Filter = "(*.xlsx)|*.xlsx";
save.Title = "另存新檔";
if (save.ShowDialog() == DialogResult.OK)
{
//取得使用者儲存Excel的路徑
path = Path.GetFullPath(save.FileName);
// 新增worksheet
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("工作表2");
//新增DataTable到sheet
worksheet.Cells["A1"].LoadFromDataTable(dt, true);
package.SaveAs(path);
}
}
重點:
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("工作表2");
worksheet.Cells["A1"].LoadFromDataTable(dt, true);
package.SaveAs(path);